# 通用接口可用sql
SQL_GENERAL = {
    # 店铺名称的未关联产品
    'unrelated_products': '''
        select
            m.asin as ASIN, min(m.product_name) as 关联产品, 
            min(s.shop_name) as 店铺名称,
            max(m.available_sale) as 可售
            from minerva_smart_stocking_master m
            join minerva_amazon_shop_master s on m.amazon_shop_id  = s.id
        where m.is_delete = 0 
            and (m.product_id = '' or m.product_id is null)
            and m.alive_flag in (0,1)
        group by m.asin, m.account_id, m.amazon_shop_id
    ''',
    # asin数量
    'asin_quantity': '''
        select count(1) as 'ASIN数量'
        from (
            select
                m.asin as ASIN, min(m.product_name) as 关联产品, 
                min(s.account_name) as 账号名称,
                max(m.available_sale) as 可售
            from minerva_smart_stocking_master m
                join minerva_amazon_shop_master s on m.amazon_shop_id  = s.id
            where m.is_delete = 0 
                and (m.product_id = '' or m.product_id is null)
                and m.alive_flag in (0,1)
            group by m.asin
            order by 账号名称
        ) v
    ''',
    # 自生产供应商未结订单的原材料组合成品
    'self_supplier_purchasing_order': '''
        select mp.project_name                                                                as 项目,
            mpo.coding                                                                     as 采购编号,
            mpois.sale_account_name                                                        as 发货账号,
            mpoi.sku                                                                       as 原材料sku,
            mpoi.product_name                                                              as 原材料产品名称,
            mpoi.purchase_quantity                                                         as 实际需采购数量,
            mpoi.stored_quantity                                                           as 已入库数量,
            mpois.stocking_sku                                                             as 成品sku,
            mpois.stocking_product_name                                                    as 成品产品名称,
            mpois.stocking_required_number * component.dosage                              as 成品需求原材料数量,
            history.shared_inventory                                                       as 已分配原材料数量,
            component.dosage                                                               as 最新用量系数,
            (mpois.stocking_required_number * component.dosage - history.shared_inventory) as 未入库数量,
            (mpois.stocking_required_number * component.dosage - history.shared_inventory) /
            component.dosage                                                               as 可生产成品数量,
            if(mpo.status = 1, '未开始', '进行中')                                         as 采购订单状态
        from minerva_purchase_order mpo
            join minerva_purchase_order_info mpoi on mpo.id = mpoi.order_id
            join minerva_purchase_order_info_source mpois on mpoi.id = mpois.info_id
            join minerva_product_component component on mpoi.product_id = component.component_id and mpois.stocking_product_id = component.parent_id
            join (
                select info_id, sale_account_id, sum(shared_inventory) as shared_inventory
                from minerva_purchase_store_history_share
                group by info_id, sale_account_id
                ) history
            on mpoi.id = history.info_id and mpois.sale_account_id = history.sale_account_id
            join minerva_project mp on mpo.project = mp.project
        where product_type = 1
        and mpo.status in (1, 2)
        and (mpois.stocking_required_number * component.dosage - history.shared_inventory) > 0;
    ''',
    # 外采供应商未结订单
    'out_supplier_purchasing_order': '''
        select mp.project_name as 项目,
            mpo.coding as 采购编号,
            mpo.remark  as 备注,
            concat(mw.name, '-', msa.name) as 账号仓库,
            mpoi.sku as sku,
            mpoi.product_name as 产品名称,
            mpoi.purchase_quantity as 实际需采购数量,
            mpoi.stored_quantity as 已入库数量,
            mpoi.purchase_quantity - mpoi.stored_quantity as 未完结数量,
            if(mpo.status = 1, '未开始', '进行中') as 采购订单状态
        from minerva_purchase_order mpo
            join minerva_purchase_order_info_external mpoi on mpo.id = mpoi.order_id
            join minerva_sale_account msa on mpoi.sale_account_id = msa.id
            join minerva_project mp on mpo.project = mp.project
            join minerva_warehouse mw on mpo.supplier_id = mw.id
        where product_type = 2
        and mpo.status in (1, 2)
        and mpo.remark  like '%采购%'
    ''',
    # 销量-库龄
    'sales_storageAge': '''
        select
            sv.snapshot_date as '快照日期',
            a.account_name as '账号名称',
            am.area_group as '群组 1欧盟 10土耳其 2英国 3美国 4加拿大',
            am.marketplace_code  as '站点',
            sv.product_name as 'sku产品名称',
            sv.asin as 'asin',
            sv.sku as 'sku库存快照-卖家sku',
            mssm.product_name as '产品名称',
            (fp.price / 100) as '成品单价',
            sv.inv_age_0_to_90_days as 'sku库存快照0-90库存',
            sv.inv_age_91_to_180_days as 'sku库存快照91-180库存',
            sv.inv_age_181_to_270_days as 'sku库存快照181-270库存',
            sv.inv_age_271_to_365_days as 'sku库存快照271-365库存',
            sv.inv_age_365_plus_days as 'sku库存快照超365库存',
            m.available_sale as 'sku可售',
            m.available_sale_day as 'sku可售天数',
            m.receiving as 'sku接受中',
            m.on_way as 'sku在途',
            m.on_way_day as 'sku在途可售天数',
            m.system_on_way as '系统在途',
            m.system_on_way_day as '系统在途可售天数',
            m.yesterday_sale_merged as 'sku昨日销量',
            m.three_days_sale_merged as 'sku近3日销量',
            m.seven_days_sale_merged as 'sku近7日销量',
            m.fourteen_days_sale_merged as 'sku近14日销量',
            m.thirty_days_sale_merged as 'sku近30日销量',
            m.total_reserved_quantity as 'sku预留'
        from (
            select fm.*
            from minerva_amazon_fba_manage_inventory fm
            join (
             select max(mi.id) as id
             from minerva_amazon_fba_manage_inventory mi
             join minerva_amazon_marketplace mam on mam.id = mi.marketplace_id
             where mi.marketplace_id != '6899964505997049868'
             group by mi.account_id, mam.area_group, mi.asin, mi.sku
            ) v on fm.id = v.id
        )sv
            join minerva_smart_stocking_inventory_sale m on m.alive_flag in (0,1) 
                and sv.account_id = m.account_id 
                    and sv.marketplace_id = m.marketplace_id 
                    and sv.asin = m.asin
                    and sv.sku = m.seller_sku
            left join minerva_amazon_shop_account a on a.id = sv.account_id
            left join minerva_amazon_marketplace am on am.id = sv.marketplace_id
            left join minerva_smart_stocking_master mssm 
            on mssm.account_id = sv.account_id and mssm.shop_id = sv.marketplace_id and mssm.asin = sv.asin
            left join minerva_finished_product fp on mssm.product_id = fp.id
        order by a.id, sv.asin, sv.sku
    ''',
    # 销量
    'sales': '''
        select
            a.account_name as '账号名称',
            s.name as '发货账号名称',
            a.shop_name as '店铺名称',
            am.marketplace_code  as '站点',
            mssm.asin as 'asin',
            fp.name as '产品名称',
            fp.sku as 'sku',
            mssm.yesterday_sale_merged as '昨日销量',
            mssm.three_days_sale_merged as '近3日销量',
            mssm.seven_days_sale_merged as '近7日销量',
            mssm.fourteen_days_sale_merged as '近14日销量',
            mssm.thirty_days_sale_merged as '近30日销量'
        from minerva_smart_stocking_master mssm
            left join minerva_amazon_shop_master a on a.id = mssm.amazon_shop_id 
            left join minerva_sale_account s on s.id = a.sale_account_id
            left join minerva_amazon_marketplace am on am.id = a.marketplace_id
            left join minerva_finished_product fp on mssm.product_id = fp.id
        where mssm.alive_flag in (0,1)
        order by a.shop_account_id, mssm.asin
    ''',
    # 跟卖asin
    'follow_sale_asin': '''
        select asin, 
            group_concat(distinct sh.account_name) ll
        from minerva_smart_stocking_master su
            join minerva_amazon_shop_account sh
            on su.account_id = sh.id
        where alive_flag in (1)
        group by asin
            having ll like '%,%'
        order by length(substring(ll, ',')) desc
    ''',
    # 库龄-财务导出-欧洲站点去重
    'storageAge_finance_Europe_distinct': '''
        select
            sv.snapshot_date as '快照日期',
            a.account_name as '账号名称',
            am.area_group as '群组 1欧盟 10土耳其 2英国 3美国 4加拿大',
            am.marketplace_code  as '站点',
            sv.product_name as 'sku产品名称',
            sv.asin as 'asin',
            sv.sku as 'sku库存快照-卖家sku',
            mssm.product_name as '产品名称',
            (fp.price / 100) as '成品单价',
            sv.inv_age_0_to_90_days as 'sku库存快照0-90库存',
            sv.inv_age_91_to_180_days as 'sku库存快照91-180库存',
            sv.inv_age_181_to_270_days as 'sku库存快照181-270库存',
            sv.inv_age_271_to_365_days as 'sku库存快照271-365库存',
            sv.inv_age_365_plus_days as 'sku库存快照超365库存',
            m.available_sale as 'sku可售',
            m.available_sale_day as 'sku可售天数',
            m.receiving as 'sku接受中',
            m.on_way as 'sku在途',
            m.on_way_day as 'sku在途可售天数',
            m.system_on_way as '系统在途',
            m.system_on_way_day as '系统在途可售天数',
            m.yesterday_sale_merged as 'sku昨日销量',
            m.three_days_sale_merged as 'sku近3日销量',
            m.seven_days_sale_merged as 'sku近7日销量',
            m.fourteen_days_sale_merged as 'sku近14日销量',
            m.thirty_days_sale_merged as 'sku近30日销量',
            m.total_reserved_quantity as 'sku预留'
        from (
            select fm.*
            from minerva_amazon_fba_manage_inventory fm
                join (
                 select max(mi.id) as id
                 from minerva_amazon_fba_manage_inventory mi
                 join minerva_amazon_marketplace mam on mam.id = mi.marketplace_id
             where mi.marketplace_id != '6899964505997049868'
             group by mi.account_id, mam.area_group, mi.asin, mi.sku
            ) v on fm.id = v.id
        )sv
            join minerva_smart_stocking_inventory_sale m on m.alive_flag in (0,1) and m.available_sale > 0 
            and sv.account_id = m.account_id 
                and sv.marketplace_id = m.marketplace_id 
                and sv.asin = m.asin
                and sv.sku = m.seller_sku
            left join minerva_amazon_shop_account a on a.id = sv.account_id
            left join minerva_amazon_marketplace am on am.id = sv.marketplace_id
            left join minerva_smart_stocking_master mssm 
            on mssm.account_id = sv.account_id and mssm.shop_id = sv.marketplace_id and mssm.asin = sv.asin
            left join minerva_finished_product fp on mssm.product_id = fp.id
        order by a.id, sv.asin, sv.sku
    ''',
    # 库龄-财务导出-欧洲站点不去重
    'storageAge_finance_Europe_not_distinct': '''
        select
            sv.snapshot_date as '快照日期',
            a.account_name as '账号名称',
            am.area_group as '群组 1欧盟 10土耳其 2英国 3美国 4加拿大',
            am.marketplace_code  as '站点',
            sv.product_name as 'sku产品名称',
            sv.asin as 'asin',
            sv.sku as 'sku库存快照-卖家sku',
            mssm.product_name as '产品名称',
            (fp.price / 100) as '成品单价',
            sv.inv_age_0_to_90_days as 'sku库存快照0-90库存',
            sv.inv_age_91_to_180_days as 'sku库存快照91-180库存',
            sv.inv_age_181_to_270_days as 'sku库存快照181-270库存',
            sv.inv_age_271_to_365_days as 'sku库存快照271-365库存',
            sv.inv_age_365_plus_days as 'sku库存快照超365库存',
            m.available_sale as 'sku可售',
            m.available_sale_day as 'sku可售天数',
            m.receiving as 'sku接受中',
            m.on_way as 'sku在途',
            m.on_way_day as 'sku在途可售天数',
            m.system_on_way as '系统在途',
            m.system_on_way_day as '系统在途可售天数',
            m.yesterday_sale_merged as 'sku昨日销量',
            m.three_days_sale_merged as 'sku近3日销量',
            m.seven_days_sale_merged as 'sku近7日销量',
            m.fourteen_days_sale_merged as 'sku近14日销量',
            m.thirty_days_sale_merged as 'sku近30日销量',
            m.total_reserved_quantity as 'sku预留'
        from (
            select fm.*
            from minerva_amazon_fba_manage_inventory fm
            join (
                     -- 站点下最新的库龄日期
                     select
                       max(snapshot_date) as snapshot_date,
                       mafmi.account_id,
                       mafmi.marketplace_id
                     from minerva_amazon_fba_manage_inventory mafmi
                     where mafmi.marketplace_id != '6899964505997049868'
                     group by mafmi.account_id, mafmi.marketplace_id
            ) vd on fm.account_id = vd.account_id
                 and fm.marketplace_id = vd.marketplace_id
                 and fm.snapshot_date = vd.snapshot_date
            where fm.marketplace_id != '6899964505997049868'
        )sv
            join minerva_smart_stocking_inventory_sale m on m.alive_flag in (0,1) and m.available_sale > 0 
            and sv.account_id = m.account_id 
                and sv.marketplace_id = m.marketplace_id 
                and sv.asin = m.asin
                and sv.sku = m.seller_sku
            left join minerva_amazon_shop_account a on a.id = sv.account_id
            left join minerva_amazon_marketplace am on am.id = sv.marketplace_id
            left join minerva_smart_stocking_master mssm 
            on mssm.account_id = sv.account_id and mssm.shop_id = sv.marketplace_id and mssm.asin = sv.asin
            left join minerva_finished_product fp on mssm.product_id = fp.id
        order by a.id, am.area_group, am.marketplace_code, sv.asin, sv.sku
    ''',
    # 导出所有asin sku 及项目
    'asin_sku_project': '''
        select
            m.shop_name as '店铺',
            p.project_name as '项目',
            s.asin,
            s.seller_sku,
            f.name as '产品名称',
            ms.fba_inventory_at as '首次库龄出现的时间'
        from minerva_smart_stocking_master ms
            left join minerva_smart_stocking_inventory_sale s 
              on s.amazon_shop_id = ms.amazon_shop_id and s.asin = ms.asin
            left join minerva_amazon_shop_master m 
              on s.amazon_shop_id = m.id
            left join minerva_finished_product f 
              on f.id = ms.product_id
            left join minerva_project p
              on p.project = f.category
        order by shop_name, asin
    ''',
    # 财务-丹丹-导出asin_sku_运营
    'asin_sku_salesperson': '''
        select 
            masm.shop_name as '店铺',
            mssm.amazon_parent_asin as '父ASIN',
            mssm.asin as 'ASIN',
            msa.name as '发货账号',
            msstis.seller_sku as '卖家sku',
            mssm.product_name as '产品名称',
            me.name as '关联运营'
        from minerva_smart_stocking_master mssm 
            join minerva_smart_stocking_inventory_sale msstis
            on mssm.amazon_shop_id  = msstis.amazon_shop_id  
            and mssm.asin = msstis.asin 
            join minerva_amazon_shop_master masm 
            on masm.id = mssm.amazon_shop_id 
            join minerva_sale_account msa 
            on masm.sale_account_id = msa.id 
            join beta_minerva_auth.minerva_employee me 
            on mssm.salesperson_id  = me.id 
        where mssm.alive_flag in (0,1)
        order by masm.shop_name, mssm.asin;
    ''',
    # 系统最新产品定位
    'latest_product_positioning': '''
        select m.shop_name          as '店铺',
           s.amazon_parent_asin as '父asin',
           s.asin               as 'asin',
           case s.final_targeting
               when 0 then '无产品等级'
               when 3 then '流量款'
               when 6 then '潜力款'
               when 9 then '清仓款'
               when 12 then '利润款'
               when 15 then '普通款（正）'
               when 18 then '普通款（负）'
               when 21 then '流量款（符合）'
               when 24 then '流量款（不符合）'
               end
                                as '产品定位',
           s.product_name       as '产品名称',
           e.name               as '运营',
           case s.is_new
               when 0 then '老品'
               when 1 then '新品'
               end
                                as '新老品'
        from minerva_smart_stocking_master s
                 join minerva_amazon_shop_master m on m.id = s.amazon_shop_id
                 left join beta_minerva_auth.minerva_employee e on s.salesperson_id = e.id
        where s.alive_flag in (0, 1)
    ''',
    # 永平-不可售数据
    'not_sale_data': '''
        select min(msa.name)          as '发货账号',
           mssm.`asin`            as 'ASIN',
           min(mssm.product_name) as '产品名称'
        from minerva_smart_stocking_master mssm
                 join minerva_amazon_shop_master masm
                      on mssm.amazon_shop_id = masm.id
                          and masm.shop_name in
                              ('Alow-EU-英国', 'Alow-EU-德国', 'Elffloor-英国', 'Elffloor-德国', 'Alow-US-美国', 'Wonth-美国',
                               'Decor Flooring-美国', 'Aliky-EU-英国', 'Aliky-EU-德国', 'Poipi-美国', 'Avinyl-美国',
                               'Apdidl-美国', 'Atrippy-美国', 'Anmon-美国', 'Aliky-美国', 'Abyssaly-美国', 'Practicalws-美国',
                               'Marble House-美国', 'Lxcreat-美国', 'Arrost-美国', 'Cosnight-美国', 'Abyssaly-加拿大',
                               'Marble House-加拿大', 'Practicalws-加拿大', 'Vonon-美国')
                          and masm.is_delete = 0
                 left join minerva_sale_account msa
                           on masm.sale_account_id = msa.id
                               and msa.is_delete = 0
        where mssm.available_sale = 0
          and mssm.is_delete = 0
      and alive_flag in (0, 1)
    group by mssm.`asin`, mssm.amazon_shop_id
    order by min(msa.name) desc;
    '''
}

